package com.bothin.experiment4.dao;

import com.bothin.experiment4.dto.StatisticDTO;
import com.bothin.experiment4.dto.SumaryDTO;
import com.bothin.experiment4.model.Employee;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.util.List;

public interface EmployeeMapper extends Mapper<Employee> {
    @Select("SELECT * FROM `employee` \n" +
            "WHERE role = 'USER_SALESMAN' \n" +
            "AND ( account LIKE #{key} OR name LIKE #{key})")
    public List<Employee> findSalesmanByKeyword(String key);

    @Select("SELECT * FROM `employee` \n" +
            "WHERE role = 'USER_STOCKMAN' \n" +
            "AND ( account LIKE #{key} OR name LIKE #{key})")
    public List<Employee> findStockmanByKeyword(String key);

    @Select("SELECT employee.name name, SUM(contact.money) value \n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "WHERE create_time BETWEEN #{start} AND #{end} \n" +
            "GROUP BY employee.employee_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}\n")
    public List<StatisticDTO> getAllSalesmanSStatistic1(String start, String end, int limit);

    @Select("SELECT employee.name name, SUM(contact.money) value \n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "GROUP BY employee.employee_id\n" +
            "ORDER BY value DESC\n" +
            "LIMIT 0,#{limit}\n")
    public List<StatisticDTO> getAllSalesmanSStatistic2(int limit);

    @Select("SELECT employee.name name, SUM(contact.money) value \n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "GROUP BY employee.employee_id\n" +
            "ORDER BY value DESC\n" )
    public List<StatisticDTO> getAllSalesmanSStatistic3();

    @Select("SELECT employee.name label, SUM(contact.money) y \n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{id} AND create_time BETWEEN #{start} AND #{end} \n" )
    public SumaryDTO getOneSalesmanSStatisticById1(String start, String end, int id);

    @Select("SELECT employee.name label, SUM(contact.money) y \n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{id} ")
    public SumaryDTO getOneSalesmanSStatisticById2(int id);

    /**
     * 查询某员工每年业绩
     * @param id
     * @return
     */
    @Select("SELECT YEAR(contact.create_time) label, SUM(contact.money) y\n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{id} \n" +
            "GROUP BY YEAR(contact.create_time)")
    public List<SumaryDTO> getTheSumEachYear(int id);

    /**
     * 查询某员工每月业绩
     * @param id
     * @return
     */
    @Select("SELECT MONTH(contact.create_time) label, SUM(contact.money) y\n" +
            "FROM `employee` LEFT JOIN contact on contact.employee_id = employee.employee_id\n" +
            "WHERE employee.employee_id = #{id} AND YEAR(contact.create_time) = #{year}\n" +
            "GROUP BY MONTH(contact.create_time)")
    public List<SumaryDTO> getTheSumEachMouth(String year,int id);

}